package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.*;
import cn.edu.sdjzu.xg.kcsj.service.ClassroomService;
import cn.edu.sdjzu.xg.kcsj.service.CourseService;
import cn.edu.sdjzu.xg.kcsj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherSignDao {
    private static TeacherSignDao teacherSignDao = null;
    private TeacherSignDao() {
    }
    public static TeacherSignDao getInstance() {
        if (teacherSignDao == null) {
            teacherSignDao = new TeacherSignDao ();
        }
        return teacherSignDao;
    }

    public Collection<TeacherSign> findAll (Connection connection,List<Condition> conditionList,Pagination pagination) throws SQLException {
        //创建StudentSign类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<TeacherSign> teacherSigns = new HashSet<TeacherSign> ();
        //创建查询学生签到的主语句
        String sql_select = "SELECT * FROM teachersign";
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_select_student =
                connection.prepareStatement(sql_select);
        //执行预编译语句
        ResultSet resultSet_teacherSign = pstmt_select_student.executeQuery();
        while (resultSet_teacherSign.next ()){
            int id = resultSet_teacherSign.getInt ("id");
            String no =  resultSet_teacherSign.getString ("no");
            int course_id = resultSet_teacherSign.getInt ("course_id");
            Course course = CourseService.getInstance ().find (course_id);
            int teacher_id = resultSet_teacherSign.getInt ("teacher_id");
            Teacher teacher = TeacherService.getInstance ().find (teacher_id);
            String signState = resultSet_teacherSign.getString ("signstate");
            String week = resultSet_teacherSign.getString ("week");
            String weekTime = resultSet_teacherSign.getString("weekTime");
            String section = resultSet_teacherSign.getString ("section");
            int classroom_id = resultSet_teacherSign.getInt ("classroom_id");
            Classroom classroom = ClassroomService.getInstance ().find (classroom_id);
            teacherSigns.add (new TeacherSign (id,no,course,teacher,signState,week,weekTime,section,classroom));
        }
        JdbcHelper.close (resultSet_teacherSign,pstmt_select_student);
        return teacherSigns;
    }

    public TeacherSign find (int id_int,Connection connection) throws SQLException {
        TeacherSign teacherSign = null;
        String find_sql = "select * from teachersign where id = ?";
        PreparedStatement pstm_teacherSign = connection.prepareStatement (find_sql);
        pstm_teacherSign.setInt (1,id_int);
        ResultSet resultSet_teacherSign = pstm_teacherSign.executeQuery ();
        if (resultSet_teacherSign.next ()){
            String no =  resultSet_teacherSign.getString ("no");
            int course_id = resultSet_teacherSign.getInt ("course_id");
            Course course = CourseService.getInstance ().find (course_id);
            int teacher_id = resultSet_teacherSign.getInt ("teacher_id");
            Teacher teacher = TeacherService.getInstance ().find (teacher_id);
            String signState = resultSet_teacherSign.getString ("signstate");
            String week = resultSet_teacherSign.getString ("week");
            String weekTime = resultSet_teacherSign.getString("weekTime");
            String section = resultSet_teacherSign.getString ("section");
            int classroom_id = resultSet_teacherSign.getInt ("classroom_id");
            Classroom classroom = ClassroomService.getInstance ().find (classroom_id);
            teacherSign = new TeacherSign (no,course,teacher,signState,week,weekTime,section,classroom);
        }
        JdbcHelper.close (resultSet_teacherSign,pstm_teacherSign);
        return teacherSign;
    }

    public Boolean add (TeacherSign teacherSignToAdd,Connection connection) throws SQLException {
        String add_sql = "INSERT INTO teachersign(no,course_id,teacher_id,signstate,week,weekTime,section,classroom_id) VALUES (?,?,?,?,?,?,?,?)";
        PreparedStatement pstm_toAdd = connection.prepareStatement (add_sql);
        pstm_toAdd.setString (1,teacherSignToAdd.getNo ());
        pstm_toAdd.setInt (2,teacherSignToAdd.getCourse ().getId ());
        pstm_toAdd.setInt (3,teacherSignToAdd.getTeacher ().getId ());
        pstm_toAdd.setString (4,teacherSignToAdd.getSignState ());
        pstm_toAdd.setString (5,teacherSignToAdd.getWeek ());
        pstm_toAdd.setString (6,teacherSignToAdd.getWeekTime ());
        pstm_toAdd.setString (7,teacherSignToAdd.getSection ());
        pstm_toAdd.setInt (8,teacherSignToAdd.getClassroom ().getId ());
        //获取执行结果
        int affectedRowNum = pstm_toAdd.executeUpdate();
        //根据结果，具体判断所要返回的值
        JdbcHelper.close(null,pstm_toAdd);
        if (affectedRowNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    public Boolean update (TeacherSign teacehrSignToUpdate,Connection connection) throws SQLException {
        String update_sql = "UPDATE teachersign SET no=?,course_id=?,teacher_id=?,signState=?,week=?,weekTime=?,section=?,classroom_id=? where id = ?";
        PreparedStatement pstm_toAdd = connection.prepareStatement (update_sql);
        pstm_toAdd.setString (1,teacehrSignToUpdate.getNo ());
        pstm_toAdd.setInt (2,teacehrSignToUpdate.getCourse ().getId ());
        pstm_toAdd.setInt (3,teacehrSignToUpdate.getTeacher ().getId ());
        pstm_toAdd.setString (4,teacehrSignToUpdate.getSignState ());
        pstm_toAdd.setString (5,teacehrSignToUpdate.getWeek ());
        pstm_toAdd.setString (6,teacehrSignToUpdate.getWeekTime ());
        pstm_toAdd.setString (7,teacehrSignToUpdate.getSection ());
        pstm_toAdd.setInt (8,teacehrSignToUpdate.getClassroom ().getId ());
        pstm_toAdd.setInt (9,teacehrSignToUpdate.getId());
        //获取执行结果
        int affectedRowNum = pstm_toAdd.executeUpdate();
        //根据结果，具体判断所要返回的值
        JdbcHelper.close(null,pstm_toAdd);
        if (affectedRowNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    public Boolean delete (int id,Connection connection) throws SQLException {
        String delete_sql = "DELETE FROM teachersign WHERE id = ?";
        PreparedStatement pstm_delete = connection.prepareStatement (delete_sql);
        pstm_delete.setInt (1,id);
        if (pstm_delete.executeUpdate () > 0){
            JdbcHelper.close(null,pstm_delete);
            return true;
        }else{
            JdbcHelper.close(null,pstm_delete);
            return false;
        }
    }
}
